var util    = require('util');
var sqlite3 = require('sqlite3');

var databasePath = "../../database/vote.sqlite3";
//var databasePath = "database/vote.sqlite3";
function Exception (message) {
	  this.message=message;
	  this.name="Exception";
}

sqlite3.verbose();

var db = undefined;
exports.connect = function(callback) {
    db = new sqlite3.Database(databasePath,
        sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE,
        function(err) {
            if (err) {
                utils.log('FAIL on creating database ' + err);
                callback(err);
            } else
                callback(null)
        }
    );
}

exports.disconnect = function(callback) {
    callback(null);
}

exports.setup = function(callback) {
	var err = function(err) {
        if (err) {
            util.log('FAIL on creating table ' + err);
            callback(error);
        } else
            callback(null);
    };
//    db.run("CREATE TABLE IF NOT EXISTS SUBWAY "+
//           "(CELL_ID INTEGER, SUBWAY_NUMBER INTEGER, "+
//           "LATITUDE INTEGER,LONGITUDE INTEGER, AUTHOR VARCHAR(10), REGDATE DATETIME)"
//        , err);
}


/**				MEMBER				**/	
exports.addMember = function(name, callback){
	exports.findMemberByName(name, function(err, row){
		if (!err) {
	          util.log('FAIL to duplicate row ' + err);
	          callback(err, null);
	          throw new Exception('FAIL to duplicate row ');
	    }
	});
	
	db.run("INSERT INTO MEMBER ( NAME ) "+
    "VALUES ( ? );"
	,
    [ name ]
    ,
    function(error) {
        if (error) {
            util.log('FAIL to addMember ' + error);
            callback(error);
        } else
            callback(null);
    });
}
exports.findMemberByName = function(name, callback){
	 var didOne = false;
	    db.each("SELECT * FROM MEMBER WHERE NAME = ?"
	    , 
        [ name ]
        , 
        function(err, row) {
	        if (err) {
	            util.log('FAIL to retrieve row ' + err);
	            callback(err, null);
	        } else {
	            if (!didOne) {
	                callback(null, row);
	                didOne = true;
	            }
	        }
	    });
}


/**				GENERAL_ELECTION				**/
exports.addGeneralElection = function(model, callback){
	db.run("INSERT INTO GENERAL_ELECTION ( name, author, regdate ) "+
    "VALUES ( ?,?,? );"
	,
    [ model.name, model.author, model.regdate ]
    ,
    function(error) {
        if (error) {
            util.log('FAIL to addGeneralElection ' + error);
            callback(error);
        } else
            callback(null);
    });
}
exports.findGeneralElectionAll = function(doEach, done){
	 db.each("SELECT * FROM GENERAL_ELECTION", function(err, row) {
	        if (err) {
	            util.log('FAIL to retrieve row ' + err);
	            done(err, null);
	        } else {
	            doEach(null, row);
	        }
	    }, done);
}


exports.add = function(model, callback) {
	if(model.subway_number == undefined){
		model.subway_number = 0;
	}
    db.run("INSERT INTO SUBWAY ( CELL_ID, SUBWAY_NUMBER, LATITUDE, LONGITUDE, AUTHOR, REGDATE) "+
        "VALUES ( ?, ? , ?, ?, ?, ? );",
        [ model.cell_id, model.subway_number, model.latitude, 
          model.longitude, model.author, new Date() ],
        function(error) {
            if (error) {
                util.log('FAIL to add ' + error);
                callback(error);
            } else
                callback(null);
        });
}

exports.remove = function(cell_id, callback) {
    db.run("DELETE FROM SUBWAY WHERE  = ?;", 
        [ cell_id ],
        function(err) {
        if (err) {
            util.log('FAIL to delete ' + err);
            callback(err);
        } else
            callback(null);
    });
}

exports.edit = function(model, callback) {
    if(model.subway_number == undefined){
		model.subway_number = 0;
	}
    db.run("UPDATE SUBWAY SET SUBWAY_NUMBER = ?, LATITUDE = ?, LONGITUDE = ?, AUTHOR = ?" +
    		"WHERE CELL_ID = ?",
        [ model.subway_number, model.latitude, 
          model.longitude, model.author, model.cell_id],
        function(error) {
            if (error) {
            	util.log('FAIL on updating table ' + err);
                callback(error);
            } else
                callback(null);
        });
}

exports.allSubways = function(callback) {
    util.log(' in allSUBWAY');
    db.all("SELECT * FROM SUBWAY", callback);
}

exports.forAll = function(doEach, done) {
    db.each("SELECT * FROM SUBWAY", function(err, row) {
        if (err) {
            util.log('FAIL to retrieve row ' + err);
            done(err, null);
        } else {
            doEach(null, row);
        }
    }, done);
}

exports.findNoteById = function(cell_id, callback) {
    var didOne = false;
    db.each("SELECT * FROM SUBWAY WHERE CELL_ID = ?", 
        [ cell_id ], 
        function(err, row) {
        if (err) {
            util.log('FAIL to retrieve row ' + err);
            callback(err, null);
        } else {
            if (!didOne) {
                callback(null, row);
                didOne = true;
            }
        }
    });
}


